<?php
class SeminarAccomodation_Controller extends AdminController {

    public $REGISTRATION_LIST_UUID = 'admin/seminarAccomodation/registrationList';
    
    /** @var SeminarAccomodationData */
    public $data;

    public function __construct() {
        parent::__construct('registration');
        $this->setDefaultListOrder('id');
        
        $this->data = $this->getSessionData()->getSeminarAccomodationData();
        
        // If we don't have a seminar specified, redirect to the seminar list.
        if (! $this->data->seminar && Router::$method != 'init') {
        	$this->redirect('/admin/seminar/all');
        }
    }
    
    /**
     * Entry method - initializes the session data.
     */
    public function init() {
    	$seminarId = $this->input->get('id');
    	
    	// Basic validation
    	if (! valid::isId($seminarId)) {
    		$this->redirectToList('error.invalidId');
    	}
    	
    	$seminar = $this->dao->fetchSeminar($seminarId, $this->getL10nLanguageCode());
    	if (! $seminar) {
    		$this->redirectToList('error.recordNotFound');
    	}
    	
    	// Create session data object
    	$this->data = new SeminarAccomodationData();
    	$this->getSessionData()->setSeminarAccomodationData($this->data);
    	$this->data->seminar = $seminar;
    	
    	$this->redirect('/admin/seminarAccomodation/assignAccomodationForm');
    }
    
    private function assignAccomodationFormCommon() {
    	if (! $this->data->seminar || ! $this->data->batch || ! $this->data->accomodationType || ! $this->data->gender) {
    		$this->redirect('/admin/seminarAccomodation/settingsForm');
    	}
    	
    	$this->viewData['formData'] = $formData;
    	$this->viewData['seminar'] = $this->data->seminar;
    	$this->viewData['seminarBatch'] = $this->data->batch;
    	$this->viewData['accomodationType'] = $this->data->accomodationType;
    	
    	if ($_GET['orderName']) {
    		$this->data->tableOrdering[$_GET['orderName']] = array(
    				'order' => $_GET['order'],
    				'orderBy' => $_GET['orderBy']
    		);
    	}
    	 
    	$this->assignAccomodationData();
    }
    
    private function assignAccomodationFormAjax($effectedIds) {
    	$this->assignAccomodationFormCommon();
    	$this->viewData['effectedIds'] = $effectedIds;
    	$this->renderForAjax('/admin/seminar/seminarAccomodationForm', 'contentBlock');
    }
    
    public function assignAccomodationForm() {
    	$this->assignAccomodationFormCommon();
    	$this->renderSubview('/admin/seminar/seminarAccomodationForm');
    }
    
    private function assignAccomodationData() {
    	$seminarId = $this->data->seminar['id'];
    	$batch = $this->data->batch;
    	$accomodationTypeCode = $this->data->accomodationType['code'];
    	
    	// Find only rooms with the given accomodation type
    	$roomListQuery = 'SELECT r.code, r.capacity, l10n.name FROM room r ' .
    			'LEFT OUTER JOIN room_l10n l10n ON r.code = l10n.room_code AND l10n.language_code = :lang ' .
    			'WHERE r.accomodation_type_code = :accomodationTypeCode ';
    	$roomListParams = array ('accomodationTypeCode' => $accomodationTypeCode, 'lang' => $this->getL10nLanguageCode());
    	if ($this->data->gender) {
    		// Select only rooms where there is not person of the oposite sex
    		$roomListQuery .= 'AND r.code NOT IN (' .
    				'SELECT reg.room_code ' .
    				'FROM registration reg ' .
    				'JOIN batch b ON b.batch_code = reg.batch_code AND b.seminar_id = reg.seminar_id ' .
    				'JOIN person per ON per.id = reg.person_id ' .
    				'WHERE reg.seminar_id = :seminarId ' .
    				'AND reg.room_code IS NOT NULL ' .
    				'AND per.gender != :gender ' .
	                'AND ((b.begin_date >= :beginDate AND b.begin_date < :endDate) OR (b.end_date > :beginDate AND b.end_date <= :endDate)) ' .
	                'AND reg.cancelled_at IS NULL) ';
    		$roomListParams['beginDate'] = $batch['begin_date'];
    		$roomListParams['endDate'] = $batch['end_date'];
    		$roomListParams['seminarId'] = $seminarId;
    		$roomListParams['gender'] = $this->data->gender;
    	}
    	$roomListQuery .= 'ORDER BY r.code';
    	$this->viewData['roomList'] = $this->dao->queryAll(
    			$roomListQuery,
    			$roomListParams
    	);
    
    	// For each room, find registrations in the given batch that have the room assigned.
    	$usableRoomCodeList = array();
    	foreach ($this->viewData['roomList'] as &$room) {
    		$usableRoomCodeList[] = $room['code'];
    		$room['registrationList'] = $this->findRegistrations(
    				$seminarId, $batch['batch_code'], $accomodationTypeCode, $room['code'], $this->data->gender,
    				$this->REGISTRATION_LIST_UUID . '-' . $room['code']);
    		if ($this->data->gender) {
    			$otherGender = $this->data->gender == 'M' ? 'F' : 'M';
    			$room['registrationListOfOtherGender'] = $this->findRegistrations($seminarId, $batch['batch_code'], $accomodationTypeCode, $room['code'], $otherGender,
    					$this->REGISTRATION_LIST_UUID . '-' . $room['code'] . '-gender');
    		}
    		$room['registrationListFromOverlappingBatches'] = $this->findRegistrationsFromOverlappingBatches(
    				$batch, $seminarId, $room['code'], $this->REGISTRATION_LIST_UUID . '-' . $room['code'] . '-over');
    	}
    
    	$this->viewData['registrationList'] = $this->findRegistrations(
    			$seminarId, $batch['batch_code'], $accomodationTypeCode, null, $this->data->gender,
    			$this->REGISTRATION_LIST_UUID);
    
    	$buildingList = $this->dao->queryAll(
    			'SELECT b.building_code, b.name ' .
    			'FROM building_l10n b ' .
    			'WHERE b.language_code = :lang ' .
    			'AND b.building_code IN (SELECT building_code FROM room) ' .
    			'ORDER BY b.name',
    			array('lang' => $this->getL10nLanguageCode()));
    	$this->viewData['roomOptions'] = array();
    	
    	// Rooms for each building.
    	// Options contain only those rooms, that are displayed.
    	$params = array(
    			'building_code' => $building['building_code'],
    			'lang' => $this->getL10nLanguageCode()
    	);
    	$query = 'SELECT r.code, l10n.name ' .
    			'FROM room r ' .
    			'JOIN room_l10n l10n ON r.code = l10n.room_code AND l10n.language_code = :lang ' .
    			'WHERE r.building_code = :building_code';
    	if (! $this->data->showAllRooms && $usableRoomCodeList) {
    		$query .= ' AND r.code IN (' . sql::inClauseValues($usableRoomCodeList) . ') ';
    	}
    	foreach ($buildingList as $building) {
    		$params['building_code'] = $building['building_code'];
    		$roomOptionsOfBuilding = $this->dao->queryOptions($query, $params);
    		if ($roomOptionsOfBuilding) {
   				$this->viewData['roomOptions'][$building['name']] = $roomOptionsOfBuilding;
    		} 
    	}
    
    	// $this->viewData['roomOptions'] = array_merge(array('NULL' => ''), $this->viewData['roomOptions']);
    }
    
    /**
     * Open the "Select batch, accType, gender dialog".
     */
    public function settingsForm() {
    	$this->settingsData();
    	
    	$this->viewData['formData'] = array(
    			'accomodationTypeCode' => $this->data->accomodationType['code'],
    			'batchCode' => $this->data->batch['batch_code'],
    			'gender' => $this->data->gender,
    			'showAllRooms' => $this->data->showAllRooms
    	);
    	
    	$this->renderSubview('/admin/seminar/seminarAccomodationSettings');
    }
    
    private function settingsData() {
    	$seminarId = $this->data->seminar['id'];
    	$batchCode = $this->data->batch['batch_code'];
    	 
    	// Batch data
    	$batchList = $this->dao->queryAll(
    			'SELECT b.batch_code, l10n.name, b.begin_date, b.end_date ' .
    			'FROM batch b ' .
    			'LEFT OUTER JOIN batch_l10n l10n ON l10n.batch_code = b.batch_code AND l10n.seminar_id = :seminar_id AND l10n.language_code = :lang ' .
    			'WHERE b.seminar_id = :seminar_id ' .
    			'ORDER BY b.begin_date',
    			array(
    					'seminar_id' => $this->data->seminar['id'],
    					'lang' => $this->getL10nLanguageCode()
    			)
    	);
    	$this->viewData['batchOptions'] = array();
    	foreach ($batchList as $batch) {
    		$this->viewData['batchOptions'][$batch['batch_code']] = $batch['name'] . ' (' . format::monthAndDayInterval($batch['begin_date'], $batch['end_date']) . ')';
    	}
    	 
    	// Accomodation type data
    	// This query selects only accomodation types that people applied for, not all available for the batch
    	$this->viewData['accomodationTypeOptions'] = $this->dao->queryOptions(
    			'SELECT at.code, l10n.name FROM accomodation_type at ' .
    			'LEFT OUTER JOIN accomodation_type_l10n l10n ON at.code = l10n.accomodation_type_code AND l10n.language_code = :lang ' .
    			'WHERE accomodation_type_code IN (' .
    				'SELECT c.accomodation_type_code ' .
    				'FROM registration r ' .
    				'JOIN category c ON c.seminar_id = r.seminar_id AND c.category_code = r.category_code ' .
    				'WHERE r.seminar_id = :seminar_id) ' .
    			'ORDER BY name',
    			array('lang' => $this->getL10nLanguageCode(), 'seminar_id' => $seminarId)
    	);
    	
    	// Gender options
    	$this->viewData['genderOptions'] = array('M' => text::get('person-gender-M'), 'F' => text::get('person-gender-F'));
    }

    /**
     * Return from the "Select batch, accType, gender dialog".
     */
    public function settings() {
    	// List of batch IDs is empty if we just came from seminarDisplay page
    	$batchCode = $this->input->post('batchCode');
    	$accomodationTypeCode = $this->input->post('accomodationTypeCode');
    	$gender = $this->input->post('gender');
    	$showAllRooms = $this->input->post('showAllRooms');
    	
    	// Validation
    	if (valid::isCode($batchCode)) {
    		$batch = $this->dao->getLocalizedBatch($this->data->seminar['id'], $batchCode, $this->getL10nLanguageCode());
    		if ($batch) {
    			$this->data->batch = $batch;
    		}
    	} 
    	
    	if (valid::isCode($accomodationTypeCode)) {
    		$accomodationType = $this->dao->getLocalizedEntityByCode('accomodation_type', $accomodationTypeCode, $this->getL10nLanguageCode());
    		if ($accomodationType) {
    			$this->data->accomodationType = $accomodationType;
    		}
    	}
    	
    	$this->data->gender = $gender;
   		$this->data->showAllRooms = $showAllRooms;
    	
    	$this->redirect('/admin/seminarAccomodation/assignAccomodationForm');
    }
    
    protected function redirectToList($errorKey=NULL) {
        if ($errorKey) {
            $this->msg->error($errorKey);
        }
        $this->redirect('/admin/seminar/all');
    }
    
    /**
     * Finds registrations assigned to given room for given batch and accomodation type.
     */
    private function findRegistrations($seminarId, $batchCode, $accomodationTypeCode, $roomCode, $gender, $tableOrderId = null) {
        $params = array(
        		'seminarId' => $seminarId,
        		'batchCode' => $batchCode,
        		'accomodationTypeCode' => $accomodationTypeCode,
        		'lang' => $this->getL10nLanguageCode(),
        		'onArrivalPaymentOrgId' => Kohana::config('yosemin.onArrivalPaymentOrganizationId')
        );
        $query = 'SELECT reg.*, ' .
                'per.first_name, per.last_name, per.birth_date, per.email, per.idcard_number, per.street, per.city, per.postal_code, per.notes AS person_notes, per.gender, ' .
                'c.code AS country_code, c.name_native AS country_name, ' .
                'at.name AS accomodation_type_name, ' .
                'reg.room_code, ' .
                'org.name payment_organization_name ' .
                'FROM registration reg ' .
                'JOIN category cat ON cat.category_code = reg.category_code AND cat.seminar_id = reg.seminar_id ' .
                'LEFT OUTER JOIN person per ON per.id = reg.person_id ' .
                'LEFT OUTER JOIN country c ON per.country_of_residence_code = c.code ' .
                'LEFT OUTER JOIN accomodation_type_l10n at ON at.accomodation_type_code = cat.accomodation_type_code AND at.language_code = :lang ' .
                'LEFT OUTER JOIN organization org ON org.id = reg.payment_organization_id ' .
                'WHERE reg.batch_code = :batchCode ' .
                'AND reg.seminar_id = :seminarId ' .
                'AND cat.accomodation_type_code = :accomodationTypeCode ' .
                'AND (reg.confirmed_at IS NOT NULL OR org.id = :onArrivalPaymentOrgId ) ' .
                'AND reg.cancelled_at IS NULL ';
        if ($gender) {
        	$query .= 'AND per.gender = :gender ';
        	$params['gender'] = $gender;
        }
        if ($roomCode) {
            $query .= 'AND reg.room_code = :room_code ';
            $params['room_code'] = $roomCode;
        }
        else {
            $query .= 'AND reg.room_code IS NULL ';
        }
        
        if ($tableOrderId && $this->data->tableOrdering[$tableOrderId]) {
        	$orderMap = $this->data->tableOrdering[$tableOrderId];
        	$query .= ' ORDER BY ' . $orderMap['orderBy'] . ' ' . $orderMap['order'];
        }
        
        return $this->dao->queryAll($query, $params);
    }
    
    /**
     * Finds registrations assigned to given room for other that the given batch which intersect with its time span.
     */
    private function findRegistrationsFromOverlappingBatches($batch, $seminarId, $roomCode) {
        $params = array(
            'batchCode' => $batch['batch_code'],
            'beginDate' => $batch['begin_date'], 
            'endDate' => $batch['end_date'], 
            'seminarId' => $seminarId, 
            'roomCode' => $roomCode, 
            'lang' => $this->getL10nLanguageCode()
        );
        $query = 'SELECT reg.*, ' .
                'per.first_name, per.last_name, per.birth_date, per.email, per.idcard_number, per.street, per.city, per.postal_code, per.notes AS person_notes, per.gender, ' .
                'c.code AS country_code, c.name_native AS country_name, ' .
                'at.name AS accomodation_type_name, ' .
                'reg.room_code, ' .
                'rl10n.name AS room_name, ' .
                'bl10n.name AS batch_name, bl10n.batch_code, b.begin_date, b.end_date ' .
                'FROM registration reg ' .
                'JOIN category cat ON cat.category_code = reg.category_code AND cat.seminar_id = reg.seminar_id ' .
                'LEFT OUTER JOIN person per ON per.id = reg.person_id ' .
                'LEFT OUTER JOIN country c ON per.country_code = c.code ' .
                'LEFT OUTER JOIN batch b ON b.batch_code = reg.batch_code AND b.seminar_id = :seminarId ' .
                'LEFT OUTER JOIN batch_l10n bl10n ON bl10n.batch_code = b.batch_code AND bl10n.seminar_id = :seminarId AND bl10n.language_code = :lang  ' .
                'LEFT OUTER JOIN room_l10n rl10n ON rl10n.room_code = :roomCode AND rl10n.language_code = :lang  ' .
                'LEFT OUTER JOIN accomodation_type_l10n at ON at.accomodation_type_code = cat.accomodation_type_code AND at.language_code = :lang ' .
                'WHERE reg.seminar_id = :seminarId ' .
                'AND reg.batch_code != :batchCode ' .
                'AND ((b.begin_date >= :beginDate AND b.begin_date < :endDate) OR (b.end_date > :beginDate AND b.end_date <= :endDate)) ' .
                'AND reg.cancelled_at IS NULL ' .
                'AND reg.room_code = :roomCode';
        
        /*
        $queryWithParams = $query;
        foreach ($params as $key => $value) {
        	$queryWithParams = str_replace(":$key", "'$value'", $queryWithParams);
        }
        echo '<p>' . $queryWithParams . '</p>';
        */
        
        return $this->dao->queryAll($query, $params);
    }
    
    public function assignOne() {
        $formData = $_REQUEST;
        $registrationId = $formData['registration_id'];
        $accomodationTypeCode = $formData['accomodation_type_code'];
        
        $seminarId = $this->data->seminar['id'];
        $batchCode = $this->data->batch['batch_code'];
        
        $registration = $this->dao->getById('registration', $registrationId);
        // $roomCode = $formData['room_code'];
        // $registration['room_code'] = $roomCode == 'NULL' ? NULL : $roomCode;
        $registration['room_code'] = $formData['room_code'] == '' ? null : $formData['room_code'];
        $this->dao->update('registration', $registration);
        
        if (request::is_ajax()) {
        	$this->assignAccomodationFormAjax($registrationId);
        }
        else {
        	$this->redirect('/admin/seminarAccomodation/assignAccomodationForm?id=' . $seminarId . '&batchCode=' . $batchCode . '&accomodationTypeCode=' . $accomodationTypeCode);
        }
    }
    
    public function assignSelected() {
        $formData = $this->postToDbValues();
        $roomCode = $formData['room_code'];
        $registrationIdList = $formData['registration_ids'];
        
        $seminarId = $this->data->seminar['id'];
        $accomodationTypeCode = $this->data->accomodationType['code'];
        
        $regIdString = '';
        if ($registrationIdList) {
	        foreach ($registrationIdList as $registrationId) {
	        	$regIdString .= $registrationId . ',';
	            $registration = $this->dao->getById('registration', $registrationId);
	            $registration['room_code'] = $roomCode == 'NULL' ? NULL : $roomCode;
	            $this->dao->update('registration', $registration);
	        }
	        // Remove the last comma
	        $regIdString = substr($regIdString, 0, strlen($regIdString) - 1);
        }

        if (request::is_ajax()) {
        	$this->assignAccomodationFormAjax($regIdString);
        }
        else {
        	$this->redirect('/admin/seminarAccomodation/assignAccomodationForm?id=' . $seminarId . '&batchCode=' . $batchCode . '&accomodationTypeCode=' . $accomodationTypeCode);
        }
    }
    
    public function overview() {
    	$this->viewData['seminar'] = $this->data->seminar;
    	
    	$usedRoomList = $this->dao->queryAll('SELECT DISTINCT r.*, l10n.name ' .
    			'FROM room r ' .
    			'JOIN room_l10n l10n ON r.code = l10n.room_code AND l10n.language_code = :lang ' .
    			'JOIN registration reg ON r.code = reg.room_code AND seminar_id = :seminar_id',
    			array('seminar_id' => $this->data->seminar['id'], 'lang' => $this->getL10nLanguageCode()));
    	$this->viewData['usedRoomList'] = $usedRoomList;
    	$this->renderSubview('/admin/seminar/seminarAccommodationOverview');
    }
    
    public function roomUsage() {
    	$roomCode = $this->input->get('room_code');
    	
    	$roomUsageModel = array();
    	
    	$registrationList = $this->dao->queryAll('SELECT r.*, p.first_name, p.last_name, p.title, b.begin_date AS batch_begin_date, b.end_date AS batch_end_date ' .
    			'FROM registration r ' .
    			'JOIN batch b ON r.batch_code = b.batch_code AND r.seminar_id = b.seminar_id ' .
    			'JOIN person p ON r.person_id = p.id ' .
    			'WHERE r.seminar_id = :seminar_id ' .
    			'AND r.room_code = :room_code ' .
    			'AND r.cancelled_at IS NULL',
    			array('seminar_id' => $this->data->seminar['id'], 'room_code' => $roomCode));

    	$seminarDays = array();
    	$timestamp = strtotime($this->data->seminar['begin_date']);
    	$endTimestamp = strtotime($this->data->seminar['end_date']);
    	while ($timestamp <= $endTimestamp) {
    		$seminarDays[] = $timestamp;
    		$timestamp += 24*3600;
    	}
    	
    	foreach ($registrationList as $registration) {
    		$daySequence = array();
    		$dayIndex = 0;
    		foreach ($seminarDays as $timestamp) {
    			$date = date::timestampToIsoFormat($timestamp);
    			if ($this->registrationIncludesNightFrom($registration, $timestamp)) {
    				$daySequence[$dayIndex] = $registration;
    			}
    			else {
    				$daySequence[$dayIndex] = '';
    			}
    			$dayIndex++;
    			if ($dayIndex > count($seminarDays) - 2) {
    				break;
    			}
    		}
    		$roomUsageModel[] = $daySequence;
    	}
    	
    	$this->viewData['seminar'] = $this->data->seminar;
    	$this->viewData['seminarDays'] = $seminarDays;
    	$this->viewData['registrationList'] = $registrationList;
    	$this->viewData['roomUsageModel'] = $roomUsageModel;
    	
    	$this->renderSubview('/admin/seminar/seminarRoomUsage');
    }
    
    private function registrationIncludesNightFrom($registration, $timestamp) {
    	$beginDate = strToTime($registration['batch_begin_date']);
    	$endDate = strToTime($registration['batch_end_date']);
    	return $beginDate <= $timestamp && $endDate > $timestamp;
    }
}
